//
//  TSCMyDBHelper.m
//  Tsc_IOS
//
//  Created by Rio on 15-5-5.
//  Copyright (c) 2015年 idream. All rights reserved.
//
// 注：IOS和Android的区别在于：IOS的APP_DB中没有_id，而是id

#import "TSCMyDBHelper.h"
#import "TSCConstants.h"
#import <sqlite3.h>
#import "TSCMyUtil.h"

@implementation TSCMyDBHelper

/**
 * 打开数据库连接。每次打开后，都需要关闭
 *
 * @date 2015-05-06
 * @author Rio
 */
+(sqlite3 *) openDB {
    // 获取数据库的路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    // 设置数据库的路径和名词
    NSString *dataBasePath = [[NSString alloc] initWithString:[documentPath stringByAppendingPathComponent:@"tsc.sqlite"]];
    // 创建并打开数据库
    sqlite3 * db;
    
    if (sqlite3_open([dataBasePath UTF8String], &db) != SQLITE_OK) {
        NSLog(@"[E]APP_DB打开失败！");
    }
    
    // TODO delete
//    NSLog(@"DB在这儿：%@", dataBasePath);
    
    return db;
}

/**
 * 关闭数据库连接
 *
 * @date 2015-05-06
 * @author Rio
 */
+(void)closeDB:(sqlite3 *)db {
    sqlite3_close(db);
}

/**
 * 初始化APP的SQLite数据库
 *
 * 注：该方法不应该运行在异步线程中，否则可能导致进入APP后，APP_DB还未准备好的情况。
 *
 * @date 2015-05-05
 * @author Rio
 */
+(void) initDB {
    // 1 打开数据库连接
    sqlite3 * db = [self openDB];
    
    // 声明局部变量
    NSMutableString *sqlBuilder = [[NSMutableString alloc]init];
    char *error;// 用于保存错误信息
    
    // 2 判断是否需要重建数据库
    [sqlBuilder appendString:@"SELECT COUNT(*) FROM sqlite_master WHERE type ='table' and name = 'tsc_city';"];
    sqlite3_stmt * statement;
    if (sqlite3_prepare_v2(db, [sqlBuilder UTF8String], -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"[E]查询APP_DB时sql语法不正确！请检查sql:%@", sqlBuilder);
        [self closeDB:db];
        return;
    }
    NSInteger tableCount = 0;
    while (sqlite3_step(statement) == SQLITE_ROW) {
        tableCount = sqlite3_column_int(statement, 0);
    }
    sqlite3_finalize(statement);
    if(tableCount == 1) {
        NSLog(@"[W]APP_DB已存在，无需重建");
        [self closeDB:db];
        return;
    }
    
    // 3 删除旧表
    NSArray *sqlDropTable =
    [NSArray arrayWithObjects:
     @"DROP TABLE IF EXISTS `tsc_city`;",
     @"DROP TABLE IF EXISTS `tsc_goods_class`;",
     @"DROP TABLE IF EXISTS `tsc_user`;",
     @"DROP TABLE IF EXISTS `tsc_biz_notice`;",
     @"DROP TABLE IF EXISTS `tsc_rl_biz_notice_pic`;",
     @"DROP TABLE IF EXISTS `tsc_sys_config`;",
     @"DROP TABLE IF EXISTS `tsc_rl_max_readed_qa`;",
     @"DROP TABLE IF EXISTS `tsc_msg`;",
     nil];
    sqlBuilder = [[NSMutableString alloc]init];
    for (NSString * sql in sqlDropTable) {
        [sqlBuilder appendString:sql];
    }
    error = nil;
    if (sqlite3_exec(db, [sqlBuilder UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]APP_DB清理旧表失败！%s", error);
        [self closeDB:db];
        return;
    }
    NSLog(@"[D]APP_DB旧表全部清理完毕");
    
    // 2 创建表
    NSArray * sqlCreateTable =
    [NSArray arrayWithObjects:
     @"CREATE TABLE IF NOT EXISTS `tsc_city` ( `id` INT PRIMARY KEY , `name` VARCHAR(20) NOT NULL DEFAULT '', `pic_url` VARCHAR(100) NOT NULL DEFAULT '', `seq` SMALLINT NOT NULL DEFAULT 0, `status` SMALLINT NOT NULL DEFAULT 0);",
     @"CREATE TABLE IF NOT EXISTS `tsc_goods_class` (  `id` INT PRIMARY KEY , `name` VARCHAR(20) NOT NULL DEFAULT '', `seq` SMALLINT NOT NULL DEFAULT 0, `status` SMALLINT NOT NULL DEFAULT 0, `pid` INT NOT NULL DEFAULT 0);",
     @"CREATE TABLE IF NOT EXISTS `tsc_user` ( `id` INT NOT NULL DEFAULT 0,`name` VARCHAR(20) NOT NULL DEFAULT '', `pic_url` VARCHAR(100) NOT NULL DEFAULT '',`email` VARCHAR(40) NOT NULL DEFAULT '' );",
     @"CREATE  TABLE IF NOT EXISTS `tsc_biz_notice` (  `id` INT NOT NULL DEFAULT 0 ,  `type` SMALLINT NOT NULL DEFAULT 0 , `city_id` INT NOT NULL DEFAULT 0, `customer_id` INT NOT NULL DEFAULT 0 ,  `guide_id` INT NOT NULL DEFAULT 0 ,  `goods_class_ids` VARCHAR(60) NOT NULL DEFAULT '' ,  `info` VARCHAR(120) NOT NULL DEFAULT '' ,  `min_price` DECIMAL(10,2) NOT NULL DEFAULT 0 ,  `max_price` DECIMAL(10,2) NOT NULL DEFAULT 0 ,  `status` SMALLINT NOT NULL DEFAULT 1 ,  `reply_count` INT NOT NULL DEFAULT 0 ,  `deal_code` VARCHAR(60) NOT NULL DEFAULT '' ,  `create_time` INT NOT NULL DEFAULT 0 ,  `begin_time` INT NOT NULL DEFAULT 0 ,  `end_time` INT NOT NULL DEFAULT 0 ,  `pid` INT NOT NULL DEFAULT 0 ,`scope` SMALLINT NOT NULL DEFAULT 1 );",
     @"CREATE  TABLE IF NOT EXISTS `tsc_rl_biz_notice_pic` (`id` INT NOT NULL DEFAULT 0,	`type` SMALLINT NOT NULL DEFAULT 0 ,`biz_notice_id` INT NOT NULL DEFAULT 0 ,`pic_url` VARCHAR(100) NOT NULL DEFAULT '' ,`seq` SMALLINT NOT NULL DEFAULT 1 );",
     @"CREATE TABLE IF NOT EXISTS `tsc_sys_config` (`key` VARCHAR(60) PRIMARY KEY, `value` VARCHAR(60) NOT NULL DEFAULT '');",
     // ==================== 下面的表仅在APP_DB存在
     @"CREATE TABLE IF NOT EXISTS `tsc_rl_max_readed_qa` (`user_id` INTEGER, `biz_notice_id` INTEGER, `max_qa_id` INT NOT NULL DEFAULT 0, PRIMARY KEY(`user_id`, `biz_notice_id`));",
     @"CREATE  TABLE IF NOT EXISTS `tsc_msg` (`user_id` INT NOT NULL,  `type` SMALLINT NOT NULL , `msg_id` INT NOT NULL ,  `sender_id` INT NOT NULL DEFAULT 0 ,  `sender_name` VARCHAR(20) NOT NULL DEFAULT '' ,  `sender_pic_url` VARCHAR(100) NOT NULL DEFAULT '' ,  `info` VARCHAR(120) NOT NULL DEFAULT '' ,  `create_time` INT NOT NULL DEFAULT 0 ,`is_readed` SMALLINT NOT NULL DEFAULT 0, `is_favorited` SMALLINT NOT NULL DEFAULT 0,  PRIMARY KEY (`user_id`, `msg_id`, `type`) );",
     // ==================== 创建索引
     @"CREATE INDEX IF NOT EXISTS `idx_user_id_type` ON `tsc_msg` (`user_id` ASC, `type` ASC) ;",
     nil];
    // 执行语句并判断是否成功
    sqlBuilder = [[NSMutableString alloc]init];
    for (NSString * sql in sqlCreateTable) {
        [sqlBuilder appendString:sql];
    }
    error = nil;
    if (sqlite3_exec(db, [sqlBuilder UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]APP_DB创建新表失败！%s", error);
        [self closeDB:db];
        return;
    }
    NSLog(@"[D]APP_DB新表全部创建完毕");
    
    // 3 初始化数据
    NSArray *sqlInsert =
    [NSArray arrayWithObjects:
     // 初始化城市
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (1,\"北京\",1,1,\"http://tscpubsys.qiniudn.com/city_bei_jing.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (2,\"上海\",2,1,\"http://tscpubsys.qiniudn.com/city_shang_hai.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (3,\"天津\",3,1,\"http://tscpubsys.qiniudn.com/city_tian_jin.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (4,\"石家庄\",4,1,\"http://tscpubsys.qiniudn.com/city_shi_jia_zhuang.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (5,\"太原\",5,1,\"http://tscpubsys.qiniudn.com/city_tai_yuan.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (6,\"呼和浩特\",6,1,\"http://tscpubsys.qiniudn.com/city_hu_he_hao_te.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (7,\"沈阳\",7,1,\"http://tscpubsys.qiniudn.com/city_shen_yang.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (8,\"长春\",8,1,\"http://tscpubsys.qiniudn.com/city_chang_chun.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (9,\"哈尔滨\",9,1,\"http://tscpubsys.qiniudn.com/city_ha_er_bin.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (10,\"南京\",10,1,\"http://tscpubsys.qiniudn.com/city_nan_jing.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (11,\"杭州\",11,1,\"http://tscpubsys.qiniudn.com/city_hang_zhou.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (12,\"合肥\",12,1,\"http://tscpubsys.qiniudn.com/city_he_fei.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (13,\"福州\",13,1,\"http://tscpubsys.qiniudn.com/city_fu_zhou.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (14,\"厦门\",14,1,\"http://tscpubsys.qiniudn.com/city_xia_men.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (15,\"南昌\",15,1,\"http://tscpubsys.qiniudn.com/city_nan_chang.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (16,\"济南\",16,1,\"http://tscpubsys.qiniudn.com/city_ji_nan.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (17,\"郑州\",17,1,\"http://tscpubsys.qiniudn.com/city_zheng_zhou.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (18,\"武汉\",18,1,\"http://tscpubsys.qiniudn.com/city_wu_han.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (19,\"长沙\",19,1,\"http://tscpubsys.qiniudn.com/city_chang_sha.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (20,\"广州\",20,1,\"http://tscpubsys.qiniudn.com/city_guang_zhou.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (21,\"南宁\",21,1,\"http://tscpubsys.qiniudn.com/city_nan_ning.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (22,\"三亚\",22,1,\"http://tscpubsys.qiniudn.com/city_san_ya.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (23,\"重庆\",23,1,\"http://tscpubsys.qiniudn.com/city_chong_qing.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (24,\"成都\",24,1,\"http://tscpubsys.qiniudn.com/city_cheng_du.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (25,\"贵阳\",25,1,\"http://tscpubsys.qiniudn.com/city_gui_yang.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (26,\"昆明\",26,1,\"http://tscpubsys.qiniudn.com/city_kun_ming.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (27,\"拉萨\",27,1,\"http://tscpubsys.qiniudn.com/city_la_sa.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (28,\"西安\",28,1,\"http://tscpubsys.qiniudn.com/city_xi_an.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (29,\"兰州\",29,1,\"http://tscpubsys.qiniudn.com/city_lan_zhou.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (30,\"西宁\",30,1,\"http://tscpubsys.qiniudn.com/city_xi_ning.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (31,\"银川\",31,1,\"http://tscpubsys.qiniudn.com/city_yin_chuan.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (32,\"乌鲁木齐\",32,1,\"http://tscpubsys.qiniudn.com/city_wu_lu_mu_qi.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (33,\"香港\",33,1,\"http://tscpubsys.qiniudn.com/city_xiang_gang.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (34,\"澳门\",34,1,\"http://tscpubsys.qiniudn.com/city_ao_men.jpg\");",
     @"INSERT INTO tsc_city (id,name,seq,status,pic_url) VALUES (35,\"台北\",35,1,\"http://tscpubsys.qiniudn.com/city_tai_bei.jpg\");",
     
     // 初始化商品分类
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (1,\"女装/女鞋/女包\",1,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (2,\"男装/男鞋/男包\",2,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (3,\"婴幼儿用品\",3,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (4,\"化妆品\",4,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (5,\"家装家具\",5,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (6,\"家电\",6,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (7,\"首饰\",7,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (8,\"钟表\",8,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (9,\"眼镜\",9,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (10,\"汽车\",10,1,0);",
     @"INSERT INTO `tsc_goods_class` (id,name,seq,status,pid) VALUES (11,\"食品/小吃店\",11,1,0);",
     
     // 测试数据 TODO
//     @"INSERT INTO tsc_user VALUES (1,'小瑾','http://7xih5t.com1.z0.glb.clouddn.com/FvQVSumbRau2g-Gh61c65jgYvEae','zrguitar@126.com');",
//     @"INSERT INTO tsc_user VALUES (2,'Rio2','http://2','zr2@126.com');",
     
     // 初始化系统配置
     [NSString stringWithFormat:@"INSERT INTO `tsc_sys_config` (key,value) VALUES(\"%@\",1);", DB_SYS_CONFIG_KEY_DB_VERSION_GOODS_CLASS],
     [NSString stringWithFormat:@"INSERT INTO `tsc_sys_config` (key,value) VALUES(\"%@\",1);", DB_SYS_CONFIG_KEY_MAX_SELECTABLE_GOODS_CLASS], // 最大可选择的商品分类数量，初始值为1，以后同商品分类一起从server更新
     [NSString stringWithFormat:@"INSERT INTO `tsc_sys_config` (key,value) VALUES(\"%@\",1);", DB_SYS_CONFIG_KEY_DB_VERSION_CITY],
     nil];
    
    // 执行语句并判断是否成功
    sqlBuilder = [[NSMutableString alloc]init];
    for (NSString * sql in sqlInsert) {
        [sqlBuilder appendString:sql];
    }
    error = nil;
    if (sqlite3_exec(db, [sqlBuilder UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]APP_DB初始化数据失败！%s", error);
        [self closeDB:db];
        return;
    }
    NSLog(@"[D]APP_DB数据全部初始化完毕");
    
    // 4 关闭数据库
    [self closeDB:db];
    NSLog(@"[D]APP_DB已关闭，全部操作完成");
}


/**
 * 将NSDictionary中的key、value分别抽取出来，并分别加以glue，分别以NSArray返回。使用场景：因多次被使用，所以进行封装。
 *
 * @date 2015-05-06
 * @author Rio
 */
/* private */ + (void) glueKeysValues:(NSDictionary *)dic keyGlue:(NSString *)keyGlue valueGlue:(NSString *)valueGlue returnedKeyArray:(NSArray **)returnedKeyArray returnedValueArray:(NSArray **)returnedValueArray {
    // 按key的顺序，依次取出各个key对应的value
    // 注：key是列名；value是列值
    NSArray * keys = [dic allKeys];
    NSMutableArray * values = [[NSMutableArray alloc]init];
    for (NSString * key in keys) {
        NSString * value = [dic objectForKey:key];
        [values addObject:value];
    }
    
    // 将key加上glue
    NSArray * keys2 = [TSCMyUtil addGlue2Array:keyGlue array:keys];
    NSArray * values2 = [TSCMyUtil addGlue2Array:valueGlue array:values];
    
    // 修改返回值
    *returnedKeyArray = keys2;
    *returnedValueArray = values2;
}

/**
 * 构造一条查询sql。WHERE条件用AND连接
 *
 * @date 2015-05-14
 * @author Rio
 */
+ (NSString *) buildSqlSelectWithTableName:(NSString *)tableName columnArray:(NSArray *)columnArray whereDictionary:(NSDictionary *)whereDictionary {
    
    // 给列名添加引号`
    NSArray * columnArray2 = [TSCMyUtil addGlue2Array:@"`" array:columnArray];
    
    // 拼装选择结果列
    NSMutableString * sql = [NSMutableString stringWithFormat:@"SELECT "];
    if([columnArray2 count] == 0) {
        [sql appendString:@"*"];
    } else {
        [sql appendString:[TSCMyUtil implodeWithGlue:@"," array:columnArray2]];
    }
    [sql appendString:[NSString stringWithFormat:@" FROM %@", tableName]];
   
    // 拼装where
    if([whereDictionary count] > 0) {
        // 获得列名、列值数组，并添加引号
        // 注：key添加`
        // 注：value添加'
        NSArray * keys = nil;
        NSArray * values = nil;
        [self glueKeysValues:whereDictionary keyGlue:@"`" valueGlue:@"'" returnedKeyArray:&keys returnedValueArray:&values];
        
        [sql appendString:@" WHERE "];
        for(NSInteger i = 0, max = [keys count]; i < max; i++) {
            if(i > 0) {
                [sql appendString:@" AND "];
            }
            
            NSString * k = [keys objectAtIndex:i];
            NSString * v = [values objectAtIndex:i];
            [sql appendString:[NSString stringWithFormat:@"%@ = %@", k, v]];
        }
    }
    
    [sql appendString:@";"];
  
    return  sql;
}

/**
 * 构造一条插入sql。使用场景：多次调用该方法生成多条插入sql，并一次性插入APP_DB，以提高效率。
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (NSString *) buildSqlInsertWithTableName:(NSString *)tableName keyValueDictionary:(NSDictionary *)keyValueDictionary {
    // 输入检查
    if([keyValueDictionary count] == 0) {
        NSLog(@"[E]拼装insert sql时，字典为空！");
        return @"";
    }
    
    // 获得列名、列值数组，并添加引号
    // 注：key添加`
    // 注：value添加'
    NSArray * keys = nil;
    NSArray * values = nil;
    [self glueKeysValues:keyValueDictionary keyGlue:@"`" valueGlue:@"'" returnedKeyArray:&keys returnedValueArray:&values];
    
    // 拼装sql
    NSMutableString * sql = [NSMutableString stringWithFormat:@"INSERT INTO %@ (", tableName];
    [sql appendString:[TSCMyUtil implodeWithGlue:@"," array:keys]];
    [sql appendString:@") VALUES ("];
    [sql appendString:[TSCMyUtil implodeWithGlue:@"," array:values]];
    [sql appendString:@");"];
    
    return sql;
}

/**
 * 构造一条更新sql。
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (NSString *) buildSqlUpdateWithTableName:(NSString *)tableName keyValueDictionary:(NSDictionary *)keyValueDictionary whereDictionary:(NSDictionary *)whereDictionary {
    // 输入检查
    if([keyValueDictionary count] == 0) {
        NSLog(@"[E]拼装update sql时，字典为空！");
        return @"";
    }
    
    // 拼装SET
    // 获得列名、列值数组，并添加引号
    // 注：key添加`
    // 注：value添加'
    NSArray * keys1 = nil;
    NSArray * values1 = nil;
    [self glueKeysValues:keyValueDictionary keyGlue:@"`" valueGlue:@"'" returnedKeyArray:&keys1 returnedValueArray:&values1];
    
    NSMutableString * sql = [NSMutableString stringWithFormat:@"UPDATE %@ SET ", tableName];
    for(NSInteger i = 0, max = [keys1 count]; i < max; i++) {
        if(i > 0) {
            [sql appendString:@","];
        }
        
        NSString * k = [keys1 objectAtIndex:i];
        NSString * v = [values1 objectAtIndex:i];
        [sql appendString:[NSString stringWithFormat:@"%@ = %@", k, v]];
    }
    
    // 拼装WHERE
    if(whereDictionary != nil && [whereDictionary count] > 0) {
        [sql appendString:@" WHERE "];
        
        // 获得列名、列值数组，并添加引号
        // 注：key添加`
        // 注：value添加'
        NSArray * keys2 = nil;
        NSArray * values2 = nil;
        [self glueKeysValues:whereDictionary keyGlue:@"`" valueGlue:@"'" returnedKeyArray:&keys2 returnedValueArray:&values2];
        
        for(NSInteger i = 0, max = [keys2 count]; i < max; i++) {
            if(i > 0) {
                [sql appendString:@" AND "];
            }
            
            NSString * k = [keys2 objectAtIndex:i];
            NSString * v = [values2 objectAtIndex:i];
            [sql appendString:[NSString stringWithFormat:@"%@ = %@", k, v]];
        }
    }
    
    [sql appendString:@";"];
    
    return sql;
}

/**
 * 构造一条删除sql。
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (NSString *) buildSqlDeleteWithTableName:(NSString *)tableName whereDictionary:(NSDictionary *)whereDictionary {
    // 拼装WHERE
     NSMutableString * sql = [NSMutableString stringWithFormat:@"DELETE FROM %@", tableName];
    if(whereDictionary != nil && [whereDictionary count] > 0) {
        [sql appendString:@" WHERE "];
        
        // 获得列名、列值数组，并添加引号
        // 注：key添加`
        // 注：value添加'
        NSArray * keys = nil;
        NSArray * values = nil;
        [self glueKeysValues:whereDictionary keyGlue:@"`" valueGlue:@"'" returnedKeyArray:&keys returnedValueArray:&values];
        
        for(NSInteger i = 0, max = [keys count]; i < max; i++) {
            if(i > 0) {
                [sql appendString:@" AND "];
            }
            
            NSString * k = [keys objectAtIndex:i];
            NSString * v = [values objectAtIndex:i];
            [sql appendString:[NSString stringWithFormat:@"%@ = %@", k, v]];
        }
    }
    
    [sql appendString:@";"];
    
    return sql;
}

/**
 * 从APP_DB中选出一条记录。WHERE条件用传入的sql。
 *
 * 注：如果一条记录都没有，返回空array
 * 注：如果SQL选出的列为NULL，dic中根据key得到的值为[NSNull null]
 *
 * @date 2015-05-14
 * @author Rio
 */
+ (NSArray *) selectWithSql:(NSString *)sql {
    // 提交查询
    NSMutableArray * arr = [NSMutableArray array];
    sqlite3 * db = [self openDB];
    sqlite3_stmt * statement;
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"[E]%s：语法不正确！请检查sql:%@",__FUNCTION__, sql);
        return arr;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        NSMutableDictionary * dic = [NSMutableDictionary dictionary];
        int columnCount = sqlite3_column_count(statement);
        for(int i = 0; i < columnCount; i++) {
            char * cstrColumnName = (char *) sqlite3_column_name(statement, i);
            char * cstrColumnValue = (char *) sqlite3_column_text(statement, i); // 如果列值不存在，则为NULL
            NSString * columnName = [NSString stringWithCString:cstrColumnName encoding:O_ENCODING];
            NSString * columnValue = cstrColumnValue == NULL ? nil : [NSString stringWithCString:cstrColumnValue encoding:O_ENCODING];
            
            if (columnValue != nil) { // SQL中正常选出列值
                [dic setObject:columnValue forKey:columnName];
            } else { // SQL中选出的列值为NULL
                [dic setObject:[NSNull null] forKey:columnName];
            }            
        }
        
        [arr addObject:dic];
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(db);
    
    return arr;
}

/**
 * 从APP_DB中选出一条记录。WHERE条件用AND连接
 *
 * @date 2015-05-14
 * @author Rio
 */
+ (NSArray *) selectWithTableName:(NSString *)tableName columnArray:(NSArray *)columnArray whereDictionary:(NSDictionary *)whereDictionary {
    
    // 拼装sql
    NSString * sql = [self buildSqlSelectWithTableName:tableName columnArray:columnArray whereDictionary:whereDictionary];
   
    // 提交查询
    NSMutableArray * arr = [NSMutableArray array];
    sqlite3 * db = [self openDB];
    sqlite3_stmt * statement;
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"[E]%s：语法不正确！请检查sql:%@",__FUNCTION__, sql);
        return arr;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        NSMutableDictionary * dic = [NSMutableDictionary dictionary];
        int columnCount = sqlite3_column_count(statement);
        for(int i = 0; i < columnCount; i++) {
            char * cstrColumnName = (char *) sqlite3_column_name(statement, i);
            char * cstrColumnValue = (char *) sqlite3_column_text(statement, i);
            NSString * columnName = [NSString stringWithCString:cstrColumnName encoding:O_ENCODING];
            NSString * columnValue = [NSString stringWithCString:cstrColumnValue encoding:O_ENCODING];
            
            [dic setObject:columnValue forKey:columnName];
        }
        
        [arr addObject:dic];
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(db);

    return arr;
}

/**
 * 向APP_DB中插入一条记录
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (void) insertWithTableName:(NSString *)tableName keyValueDictionary:(NSDictionary *)keyValueDictionary {
    // 拼装sql
    NSString * sql = [self buildSqlInsertWithTableName:tableName keyValueDictionary:keyValueDictionary];
    
    // 执行sql
    sqlite3 * db = [TSCMyDBHelper openDB];
    char * error;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]insert失败！%s", error);
        [TSCMyDBHelper closeDB:db];
        return;
    }
    [self closeDB:db];
}

/**
 * 在APP_DB中更新一条记录
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (void) updateWithTableName:(NSString *)tableName keyValueDictionary:(NSDictionary *)keyValueDictionary whereDictionary:(NSDictionary *)whereDictionary {
    // 拼装sql
    NSString * sql = [self buildSqlUpdateWithTableName:tableName keyValueDictionary:keyValueDictionary whereDictionary:whereDictionary];

    // 执行sql
    sqlite3 * db = [TSCMyDBHelper openDB];
    char * error;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]update失败！%s", error);
        [TSCMyDBHelper closeDB:db];
        return;
    }
    [self closeDB:db];
}

/**
 * 从APP_DB中删除一条记录
 *
 * @date 2015-05-06
 * @author Rio
 */
+ (void) deleteWithTableName:(NSString *)tableName whereDictionary:(NSDictionary *)whereDictionary {
    // 拼装sql
    NSString * sql = [self buildSqlDeleteWithTableName:tableName whereDictionary:whereDictionary];
    
    // 执行sql
    sqlite3 * db = [TSCMyDBHelper openDB];
    char * error;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"[E]delete失败！%s", error);
        [TSCMyDBHelper closeDB:db];
        return;
    }
    [self closeDB:db];
}

@end
